import io
import os
import sqlite3
import urllib
from datetime import datetime, timedelta
from typing import Optional

import pandas as pd
from fastapi import HTTPException, APIRouter, Depends, Response, Query
from pydantic import BaseModel
from starlette import status
from starlette.responses import StreamingResponse, FileResponse

import jwt
from fastapi.security import HTTPBasic, HTTPBasicCredentials

from server.tools import calculate_parking_fee, get_active_plates, get_time_range_info_plates, get_unactive_plates

plates_router = APIRouter()

# 查询接口
@plates_router.get("/all")
async def get_plates(limit: Optional[int] = 10):
    conn = sqlite3.connect('./license_plates.db')
    conn.row_factory = sqlite3.Row  # 返回字典格式
    c = conn.cursor()
    c.execute("SELECT * FROM plates ORDER BY enter_time DESC LIMIT ?", (limit,))
    rows = c.fetchall()
    conn.close()
    return {"plates": [dict(row) for row in rows]}

# 文件下载接口
@plates_router.get("/download")
async def download_file(image_name: str):
    image_path = os.path.join("./parking_images", image_name)

    if not os.path.exists(image_path):
        return {"error": "图片不存在"}

    # 返回文件响应
    return FileResponse(image_path)

@plates_router.get("/active")
async def get_active_plates_route():
    active_plates = get_active_plates()  # 调用已有的函数获取数据
    return {"plates": active_plates}

@plates_router.get("/unactive")
async def get_unactive_plates_route():
    active_plates = get_unactive_plates()  # 调用已有的函数获取数据
    return {"plates": active_plates}

class PlateRequest(BaseModel):
    plate_number: str
@plates_router.post("/offCar", status_code=status.HTTP_200_OK)
async def offCar(request: PlateRequest):
    plate = request.plate_number

    conn = sqlite3.connect('./license_plates.db')
    c = conn.cursor()

    # 查询当前在场车辆
    c.execute(
        "SELECT id, enter_time, location, filename FROM current_plates WHERE plate_number = ?",
        (plate,)
    )
    current_plate = c.fetchone()

    # 计算停车费用和时长
    enter_time = datetime.strptime(current_plate[1], "%Y-%m-%d %H:%M:%S")
    exit_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    exit_time_dt = datetime.strptime(exit_time, "%Y-%m-%d %H:%M:%S")
    parking_seconds = (exit_time_dt - enter_time).total_seconds()
    total_fee = calculate_parking_fee(parking_seconds)

        # **更新 plates 表中的历史记录**
    c.execute(
        """
        UPDATE plates
        SET exit_time       = ?,
            exit_filename   = ?,
            parking_seconds = ?,
            total_fee       = ?
        WHERE plate_number = ?
          AND exit_time IS NULL
        """,
        (exit_time, "", parking_seconds, total_fee, plate,)
    )

    # 删除当前在场记录
    c.execute("DELETE FROM current_plates WHERE plate_number = ?", (plate,))
    conn.commit()
    conn.close()

    return {
        "status": "success",
        "message": "车辆已成功出场",
        "data": {"plate_number": plate}
    }

@plates_router.post("/offCarAbnormal", status_code=status.HTTP_200_OK)
async def offCarAbnormal(request: PlateRequest):
    plate = request.plate_number

    conn = sqlite3.connect('./license_plates.db')
    c = conn.cursor()

    # 查询当前在场车辆
    c.execute(
        "SELECT id, enter_time, location, filename FROM abnormal_plates WHERE plate_number = ?",
        (plate,)
    )
    current_plate = c.fetchone()

    # 计算停车费用和时长
    enter_time = datetime.strptime(current_plate[1], "%Y-%m-%d %H:%M:%S")
    exit_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    exit_time_dt = datetime.strptime(exit_time, "%Y-%m-%d %H:%M:%S")
    parking_seconds = (exit_time_dt - enter_time).total_seconds()
    total_fee = calculate_parking_fee(parking_seconds)

        # **更新 plates 表中的历史记录**
    c.execute(
        """
        UPDATE plates
        SET exit_time       = ?,
            exit_filename   = ?,
            parking_seconds = ?,
            total_fee       = ?
        WHERE plate_number = ?
          AND exit_time IS NULL
        """,
        (exit_time, "", parking_seconds, total_fee, plate,)
    )

    # 删除当前在场记录
    c.execute("DELETE FROM abnormal_plates WHERE plate_number = ?", (plate,))
    conn.commit()
    conn.close()

    return {
        "status": "success",
        "message": "车辆已成功出场",
        "data": {"plate_number": plate}
    }

@plates_router.get("/getTimeRangeInfo")
async def get_time_range_info(start_time: str, end_time: str):
    """
    获取不同时间段，进出车辆信息，统计出车辆的费用。
    """
    active_plates = get_time_range_info_plates(start_time, end_time)  # 调用已有的函数获取数据
    return active_plates


@plates_router.get("/export_excel")
async def export_excel(
        start_time: str = Query(..., description="开始日期（YYYY-MM-DD）"),
        end_time: str = Query(..., description="结束日期（YYYY-MM-DD）")
):
    data = get_time_range_info_plates(start_time, end_time)  # 调用已有的函数获取数据
    print(data)
    # 转换为 DataFrame
    df = pd.DataFrame(data["data"])

    # 删除 id 和 filename 列
    df = df.drop(columns=["id", "filename", "exit_filename", "total_fee", "parking_seconds", "create_time"])

    # 将 exit_time 中的 None 替换为 "未退出"
    df["exit_time"] = df["exit_time"].fillna("未退出")

    # 计算停车时长
    def calculate_current_parking_time(enter_time, exit_time=None):
        enter = datetime.strptime(enter_time, "%Y-%m-%d %H:%M:%S")
        if exit_time == "未退出":
            exit = datetime.now()
        else:
            exit = datetime.strptime(exit_time, "%Y-%m-%d %H:%M:%S")
        diff = (exit - enter).total_seconds()
        if diff < 0:
            return '异常'
        hours = int(diff // 3600)
        minutes = int((diff % 3600) // 60)
        return f"{hours}小时{minutes}分钟"

    df["parking_duration"] = df.apply(lambda row: calculate_current_parking_time(row["enter_time"], row["exit_time"]),
                                      axis=1)

    # 将列名翻译成中文
    column_mapping = {
        "plate_number": "车牌号",
        "enter_time": "入场时间",
        "exit_time": "出场时间",
        "location": "位置",
        "parking_duration": "停车时长",
        "cost": "费用"
    }
    df = df.rename(columns=column_mapping)

    # 计算总收入
    total_cost = df["费用"].sum()

    # 创建包含所有列的行，仅在"费用"列显示总收入
    new_row = pd.Series({"车牌号": "总收入", "入场时间": "", "出场时间": "", "位置": "", "停车时长": "", "费用": total_cost})
    df = pd.concat([df, new_row.to_frame().T], ignore_index=True)

    # 创建一个内存中的文件对象
    output = io.BytesIO()

    # 使用 ExcelWriter 将 DataFrame 写入内存中的文件对象
    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="Sheet1", index=False)

    # 移动文件指针到文件开头
    output.seek(0)

    # 返回文件响应
    return StreamingResponse(
        output,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={"Content-Disposition": "attachment; filename=plates_data.xlsx"}
    )


@plates_router.post("/save_fee_settings")
async def save_fee_settings(settings: dict):
    conn = sqlite3.connect('./license_plates.db')
    c = conn.cursor()

    # 插入或更新费用设置
    c.execute('''
        INSERT INTO fee_settings (fee_within_1_hour, fee_after_1_hour, fee_within_24_hours)
        VALUES (?, ?, ?)
        ON CONFLICT(id) DO UPDATE 
        SET fee_within_1_hour = excluded.fee_within_1_hour,
            fee_after_1_hour = excluded.fee_after_1_hour,
            fee_within_24_hours = excluded.fee_within_24_hours
    ''', (settings['feeWithin1Hour'], settings['feeAfter1Hour'], settings['feeWithin24Hours']))

    conn.commit()
    conn.close()

    return {"message": "费用设置已保存"}


# 加载费用设置
@plates_router.get("/load_fee_settings")
async def load_fee_settings():
    conn = sqlite3.connect('./license_plates.db')
    conn.row_factory = sqlite3.Row  # 返回字典格式
    c = conn.cursor()

    # 查询最新的费用设置
    c.execute('''
            SELECT * FROM fee_settings
            ORDER BY id DESC
            LIMIT 1
        ''')

    row = c.fetchone()
    conn.close()

    if row:
        fee_settings = dict(row)
        return dict(row)
    else:
        raise HTTPException(status_code=404, detail="费用设置未找到")


SECRET_KEY = "your-secret-key-123"  # 请替换为环境变量
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 30  # 令牌有效期30分钟
security = HTTPBasic()


# 生成JWT令牌
def create_access_token(data: dict, expires_delta: Optional[timedelta] = None):
    to_encode = data.copy()
    if expires_delta:
        expire = datetime.utcnow() + expires_delta
    else:
        expire = datetime.utcnow() + timedelta(minutes=15)
    to_encode.update({"exp": expire})
    encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)
    return encoded_jwt

class LoginRequest(BaseModel):
    type: int
# 登录接口
@plates_router.post("/login")
async def login(login_request: LoginRequest, credentials: HTTPBasicCredentials = Depends(security)):
    print("Type:", login_request.type)
    conn = sqlite3.connect('./license_plates.db')
    c = conn.cursor()
    if login_request.type == 0:
        # 查询用户（包含密码验证）
        c.execute(
            "SELECT * FROM admin WHERE username = ? AND password = ?",
            (credentials.username, credentials.password)
        )
        user = c.fetchone()
        conn.close()

        if not user:
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="用户名或密码错误",
                headers={"WWW-Authenticate": "Basic"},
            )

        # 生成JWT令牌（确保user有正确索引）
        access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES)
        access_token = create_access_token(
            data={"sub": user[1], "role": user[3] if len(user) > 3 else "user"},  # 处理role可能不存在的情况
            expires_delta=access_token_expires
        )

        return {
            "access_token": access_token,
            "token_type": "bearer",
            "user_info": {
                "username": user[1],
                "role": "admin"
            }
        }
    elif login_request.type == 1:

        # 查询用户（包含密码验证）
        c.execute(
            "SELECT * FROM user WHERE username = ? AND password = ?",
            (credentials.username, credentials.password)
        )
        user = c.fetchone()
        conn.close()

        if not user:
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="用户名或密码错误",
                headers={"WWW-Authenticate": "Basic"},
            )

        # 生成JWT令牌（确保user有正确索引）
        access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES)
        access_token = create_access_token(
            data={"sub": user[1], "role": user[3] if len(user) > 3 else "user"},  # 处理role可能不存在的情况
            expires_delta=access_token_expires
        )

        return {
            "access_token": access_token,
            "token_type": "bearer",
            "user_info": {
                "username": user[1],
                "role": "user"
            }
        }

# 注册接口
@plates_router.post("/register")
async def register(credentials: HTTPBasicCredentials = Depends(security)):
    conn = sqlite3.connect('./license_plates.db')
    c = conn.cursor()

    try:
        c.execute(
            "INSERT INTO user (username, password) VALUES (?, ?)",
            (credentials.username, credentials.password)
        )
        conn.commit()
    except sqlite3.IntegrityError:
        conn.rollback()
        raise HTTPException(
            status_code=400,
            detail="用户名已存在"
        )
    finally:
        conn.close()

    return {"message": "注册成功"}

